﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class ANALYZEBATCHData : BaseFunction
    {
        
        public ANALYZEBATCHData()
            : base()
        {
           
        }
        public ANALYZEBATCHData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet GetRawData(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string FROMDATE,ENDDATE,LOT;
            FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            LOT = dwABPOS["LOT"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
          
                #region
                or1.CommandText = " SELECT trim(A.WBBDPART) AS FDPART,TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR AS FARTICLE, "
                    + " MAX(B.WBEWBUCHDAT) AS FDAT,SUM(ROUND(B.WBEWMTR*0.001/0.9144,1)) AS FQTY, "
                    + " Y.GDAT,Y.GQTY  FROM WBBST A,WBBEW B, "
                    + " (SELECT trim(A.WBBDPART) AS GDPART,TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR AS GARTICLE, "
                    + " MAX(B.WBEWBUCHDAT) AS GDAT,SUM(B.WBEWMTR*0.001) AS GQTY "
                    + " FROM WBBST A,WBBEW B WHERE A.WBBMANDANT = B.WBEWMANDANT AND A.WBBRWKRS = B.WBEWRWKRS "
                    + " AND A.WBBRWLFD = B.WBEWRWLFD AND A.WBBPOS = B.WBEWPOS AND A.WBBMANDANT = '00' AND B.WBEWLO = 'HL' "
                    + " AND A.WBBWAKZ = 'R' AND A.WBBDPART like '" + LOT + "%'  "
                    + " AND ((B.WBEWBS = '13' AND B.WBEWFC = 'WB15    ')  OR B.WBEWBS IN ('01','10','11')) "
                    + " AND NOT EXISTS (SELECT '1' FROM BABST A1  WHERE A1.BMMANDANT = A.WBBMANDANT  "
                    + " AND A1.BMLEER = ' ' AND TRIM(A1.BMBEM) = TRIM(A.WBBDPART)) "
                    + " AND NOT EXISTS (SELECT '1' FROM WBBST A2 WHERE A2.WBBMANDANT = A.WBBMANDANT "
                    + " AND  TRIM(A2.WBBDPART) = TRIM(A.WBBDPART) AND A2.WBBWAKZ = 'R' AND A2.WBBVERSANDKZ = 'N') "
                    + " GROUP BY A.WBBDPART,TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR ) Y "
                    + " WHERE A.WBBMANDANT = B.WBEWMANDANT AND A.WBBRWKRS = B.WBEWRWKRS "
                    + " AND A.WBBRWLFD = B.WBEWRWLFD AND A.WBBPOS = B.WBEWPOS AND A.WBBMANDANT = '00' AND B.WBEWLO = 'HL' "
                    + " AND A.WBBWAKZ = 'F' AND A.WBBDPART like '" + LOT + "%'  AND TRIM(A.WBBDPART) = Y.GDPART(+) "
                    + " AND TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR = Y.GARTICLE(+) "
                    + " AND ((B.WBEWBS = '13' AND B.WBEWFC = 'WB16    ') "
                    + "    OR (B.WBEWBS IN ('01','10','11') AND B.WBEWLB <>'TH')) "
                    + " AND NOT EXISTS (SELECT '1' FROM BABST A1 WHERE A1.BMMANDANT = A.WBBMANDANT  "
                    + " AND A1.BMLEER = ' ' AND TRIM(A1.BMBEM) = TRIM(A.WBBDPART)) AND NOT EXISTS (SELECT '1' FROM WBBST A2 "
                    + " WHERE A2.WBBMANDANT = A.WBBMANDANT AND  TRIM(A2.WBBDPART) = TRIM(A.WBBDPART) AND A2.WBBWAKZ = 'R' "
                    + " AND A2.WBBVERSANDKZ = 'N' ) "
                    + " GROUP BY A.WBBDPART,TRIM(A.WBBART)||'-'||TRIM(A.WBBFARBE)||'-'||A.WBBVAR,Y.GDAT,Y.GQTY "
                    + " HAVING MAX(B.WBEWBUCHDAT) >='" + FROMDATE + "' AND  MAX(B.WBEWBUCHDAT) <= '" + ENDDATE + "' "
                    ;
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "ANALYZEBATCH";
                   /* 
                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dwTemp in setLocal.Tables["ANALYZEBATCH"].Rows)
                    {
                        ColumnName = "ARTICLE";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        ColumnName = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        ColumnName = "SUPPLIER";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value; 

                        ColumnName = "FACTORY";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                      
                       
                    }
                     */
                    
                    set4Return.Merge(setLocal);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }        
        public DataSet BuildGBBEW(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetRawData(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}